package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.List;


import org.apache.commons.collections.CollectionUtils;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.UploadFileActionLogDao;
import com.feib.stms.dao.jdbc.rowmapper.UploadFileActionLogRowMapper;
import com.feib.stms.model.UploadFile;
import com.feib.stms.model.UploadFileActionLog;

@Repository("uploadFileActionLogDao")
public class UploadFileActionLogJdbcDao extends BaseJdbcDao implements UploadFileActionLogDao {

	private OracleSequenceMaxValueIncrementer sequence;

	private SimpleJdbcInsert uploadFileActionLogJdbcInsert;


	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "UPLOAD_FILE_ACTION_LOG_PK_SEQ");		
		this.uploadFileActionLogJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.uploadFileActionLogJdbcInsert.withTableName("UPLOAD_FILE_ACTION_LOG")
		.usingColumns("UPLOAD_FILE_ACTION_LOG_PK", "UPLOAD_FILE_PK", "USER_PK" ,"ACTION_TIME" ,"ACTION");

	}	

	public int insert(UploadFileActionLog uploadFileActionLog) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		Long uploadFileActionLogPk = new Long(this.sequence.nextLongValue());
		parameters.addValue("UPLOAD_FILE_ACTION_LOG_PK",  uploadFileActionLogPk, Types.NUMERIC);
		parameters.addValue("UPLOAD_FILE_PK",uploadFileActionLog.getUploadFile().getUploadFilePk(), Types.NUMERIC);
		parameters.addValue("USER_PK",  uploadFileActionLog.getUser().getUserPk(), Types.NUMERIC);
		parameters.addValue("ACTION_TIME", uploadFileActionLog.getActionTime() , Types.TIMESTAMP);
		parameters.addValue("ACTION", uploadFileActionLog.getAction(), Types.VARCHAR);
		
		int insertCounts = this.uploadFileActionLogJdbcInsert.execute(parameters);
		if (insertCounts > 0)
			uploadFileActionLog.setUploadFileActionLogPk(uploadFileActionLogPk);
		return insertCounts;
	}


	public List<UploadFileActionLog> queryUploadFileActionLog(UploadFile uploadFile) {
		StringBuffer sql = new StringBuffer();
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		sql.append("SELECT L.*,");
		sql.append(" U.USER_NAME ,C.CODE_NAME AS ACTION_NAME");
		sql.append(" FROM UPLOAD_FILE_ACTION_LOG L,APP_USER U ,CODE_TABLE C");
		sql.append(" WHERE  L.USER_PK= U.USER_PK  AND L.UPLOAD_FILE_PK = :UPLOAD_FILE_PK  ");
		parameters.addValue("UPLOAD_FILE_PK",  uploadFile.getUploadFilePk(), Types.NUMERIC);
		sql.append(" AND L.ACTION = C.CODE_VALUE AND C.CODE_KIND = 'UPLOAD_FILE_ACTION_LOG' order by ACTION_TIME");		

		return this.getSimpleJdbcTemplate().query(sql.toString(), new UploadFileActionLogRowMapper(), parameters);
	}


	public int countUploadFileActionLog(Long uploadFilePk,List<String> action ,Long userPk ) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("SELECT  COUNT(*)  FROM UPLOAD_FILE_ACTION_LOG L " );
		sql.append("WHERE UPLOAD_FILE_PK = :UPLOAD_FILE_PK  ");
		parameters.addValue("UPLOAD_FILE_PK",  uploadFilePk, Types.NUMERIC);


		if (CollectionUtils.isNotEmpty(action))
		{
			if (1 == action.size())
			{
				sql.append(" AND ACTION = :ACTION");
				parameters.addValue("ACTION",action.get(0) , Types.VARCHAR);
			}
			else
			{
				sql.append(" AND ACTION IN (:ACTION)");
				parameters.addValue("ACTION",action , Types.VARCHAR);
			}
		}	


		/*操作人員 可不必輸入 */
		if( null!=userPk){
			sql.append(" AND USER_PK  = :USER_PK ");
			parameters.addValue("USER_PK",  userPk, Types.NUMERIC);
		}

		int uploadFileActionLogCount = this.getSimpleJdbcTemplate().queryForInt(sql.toString(), parameters);
		return uploadFileActionLogCount;
	}


}
